<?php

require_once 'db_config.php';

class StaffDB {

    // private -> public
    public $pdo;
    
    // 表名定义
    private $tables = [
        'staff' => 'cs_staff',
        'qualifications' => 'cs_staff_qualifications',
        'organizations' => 'cs_staff_organizations',
        'groups' => 'cs_staff_groups',
        'available_seats' => 'cs_staff_available_seats',
        'marks' => 'cs_staff_marks',
        'temporary_roles' => 'cs_temporary_roles',
        'temp_role_qualifications' => 'cs_temporary_role_qualifications',
        'temp_role_organizations' => 'cs_temporary_role_organizations',
        'temp_role_seats' => 'cs_temporary_role_available_seats'
    ];
    
    public function __construct() {
        // 从全局变量获取数据库配置
        if (!isset($GLOBALS['dbConfig'])) {
            throw new Exception("数据库配置未定义，请先定义全局变量\$GLOBALS['dbConfig']");
        }
        
        $dbConfig = $GLOBALS['dbConfig'];
        
        try {
            $dsn = "mysql:host={$dbConfig['host']};dbname={$dbConfig['dbname']};charset=utf8mb4";
            $this->pdo = new PDO(
                $dsn,
                $dbConfig['username'],
                $dbConfig['password'],
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
                ]
            );
        } catch (PDOException $e) {
            throw new Exception("数据库连接失败: " . $e->getMessage());
        }
    }
    
    /**
     * 生成UUID (GUID)
     * private ->public
     */
    public function generateGuid() {
        return sprintf(
            '%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
            mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0x0fff) | 0x4000,
            mt_rand(0, 0x3fff) | 0x8000,
            mt_rand(0, 0xffff),
            mt_rand(0, 0xffff),
            mt_rand(0, 0xffff)
        );
    }
    
    /**
     * 创建新员工
     */
    public function createStaff($data) {
        // 生成GUID（如果未提供）
        $guid = isset($data['guid']) ? $data['guid'] : $this->generateGuid();
        
        try {
            $this->pdo->beginTransaction();
            
            // 插入员工主表
            $stmt = $this->pdo->prepare("
                INSERT INTO {$this->tables['staff']} (guid, id, name)
                VALUES (:guid, :id, :name)
            ");
            $stmt->execute([
                ':guid' => $guid,
                ':id' => $data['id'],
                ':name' => $data['name']
            ]);
            
            // 处理关联表数据
            $this->insertRelations($guid, $data);
            
            $this->pdo->commit();
            return $guid;
        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw new Exception("创建员工失败: " . $e->getMessage());
        }
    }
    
    /**
     * 通过GUID获取员工信息
     */
    // public function getStaffByGuid($guid) {
    //     // 获取主表信息
    //     $stmt = $this->pdo->prepare("
    //         SELECT * FROM {$this->tables['staff']}
    //         WHERE guid = :guid
    //     ");
    //     $stmt->execute([':guid' => $guid]);
    //     $staff = $stmt->fetch();
        
    //     if (!$staff) {
    //         return null;
    //     }
        
    //     // 获取关联表信息
    //     $staff['qualifications'] = $this->getQualifications($guid);
    //     $staff['organizations'] = $this->getOrganizations($guid);
    //     $staff['groups'] = $this->getGroups($guid);
    //     $staff['availableSeats'] = $this->getAvailableSeats($guid);
    //     $staff['marks'] = $this->getMarks($guid);
    //     $staff['temporaryRoles'] = $this->getTemporaryRoles($guid);
        
    //     return $staff;
    // }
    /**
     * 通过GUID获取员工信息
     */
    public function getStaffByGuid($guid) {
        // 获取主表信息
        $stmt = $this->pdo->prepare("
            SELECT * FROM {$this->tables['staff']}
            WHERE guid = :guid
        ");
        $stmt->execute([':guid' => $guid]);
        $staff = $stmt->fetch();
        
        if (!$staff) {
            return null;
        }
        
        // 获取关联表信息 - 改为平铺结构
        $qualificationsData = $this->getQualifications($guid);
        $staff['qualifications'] = $qualificationsData['qualifications'];
        $staff['qualificationIds'] = $qualificationsData['qualificationIds'];
        
        $organizationsData = $this->getOrganizations($guid);
        $staff['organizations'] = $organizationsData['organizations'];
        $staff['organizationsIds'] = $organizationsData['organizationsIds'];
        
        $groupsData = $this->getGroups($guid);
        $staff['groups'] = $groupsData['groups'];
        $staff['groupIds'] = $groupsData['groupIds'];
        
        $availableSeatsData = $this->getAvailableSeats($guid);
        $staff['availableSeats'] = $availableSeatsData['availableSeats'];
        $staff['availableSeatIds'] = $availableSeatsData['availableSeatIds'];
        
        $marksData = $this->getMarks($guid);
        $staff['marks'] = $marksData['marks'];
        $staff['markIds'] = $marksData['markIds'];
        
        // 临时角色也需要修改结构
        $temporaryRoles = $this->getTemporaryRoles($guid);
        $staff['temporaryRoles'] = $this->flattenTemporaryRoles($temporaryRoles);
        
        return $staff;
    }

    /**
     * 将临时角色数据结构平铺
     */
    private function flattenTemporaryRoles($temporaryRoles) {
        $flattened = [];
        foreach ($temporaryRoles as $role) {
            $flattenedRole = [
                'from' => $role['from'],
                'to' => $role['to'],
                'qualifications' => $role['qualifications']['qualifications'],
                'qualificationIds' => $role['qualifications']['qualificationIds'],
                'organizations' => $role['organizations']['organizations'],
                'organizationsIds' => $role['organizations']['organizationsIds'],
                'availableSeats' => $role['availableSeats']['availableSeats'],
                'availableSeatIds' => $role['availableSeats']['availableSeatIds']
            ];
            $flattened[] = $flattenedRole;
        }
        return $flattened;
    }
        
    /**
     * 更新员工信息
     */
    public function updateStaff($data) {
        if (!isset($data['guid'])) {
            throw new Exception("更新员工信息必须提供GUID");
        }
        
        $guid = $data['guid'];
        
        try {
            $this->pdo->beginTransaction();
            
            // 更新主表信息
            $stmt = $this->pdo->prepare("
                UPDATE {$this->tables['staff']}
                SET id = :id, name = :name
                WHERE guid = :guid
            ");
            $stmt->execute([
                ':id' => $data['id'],
                ':name' => $data['name'],
                ':guid' => $guid
            ]);
            
            // 先删除所有关联数据
            $this->deleteRelations($guid);
            
            // 插入新的关联数据
            $this->insertRelations($guid, $data);
            
            $this->pdo->commit();
            return true;
        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw new Exception("更新员工失败: " . $e->getMessage());
        }
    }
    
    /**
     * 删除员工信息
     */
    public function deleteStaff($guid) {
        try {
            $stmt = $this->pdo->prepare("
                DELETE FROM {$this->tables['staff']}
                WHERE guid = :guid
            ");
            $stmt->execute([':guid' => $guid]);
            return $stmt->rowCount() > 0;
        } catch (Exception $e) {
            throw new Exception("删除员工失败: " . $e->getMessage());
        }
    }

     /**
     * 获取员工列表（仅包含基本信息：guid、id、name）
     * @return array 员工列表数组
     * @throws Exception 当查询失败时抛出异常
     */
    public function getStaffList() {
        try {
            $stmt = $this->pdo->prepare("
                SELECT guid, id, name 
                FROM {$this->tables['staff']}
                ORDER BY name ASC
            ");
            $stmt->execute();
            return $stmt->fetchAll();
        } catch (Exception $e) {
            throw new Exception("获取员工列表失败: " . $e->getMessage());
        }
    }

    /**
     * 获取全部员工的完整信息（包含所有关联数据）
     * @return array 包含所有员工完整信息的数组
     * @throws Exception 当查询失败时抛出异常
     */
    public function getAllStaff() {
        try {
            // 先获取所有员工的guid
            $stmt = $this->pdo->prepare("
                SELECT guid 
                FROM {$this->tables['staff']}
                ORDER BY name ASC
            ");
            $stmt->execute();
            $guids = $stmt->fetchAll(PDO::FETCH_COLUMN, 0); // 只提取guid列
            
            $allStaff = [];
            foreach ($guids as $guid) {
                $staffInfo = $this->getStaffByGuid($guid);
                if ($staffInfo) {
                    $allStaff[] = $staffInfo;
                }
            }
            
            return $allStaff;
        } catch (Exception $e) {
            throw new Exception("获取全部员工信息失败: " . $e->getMessage());
        }
    }

    /**
     * 按条件筛选员工（支持临时角色筛选）
     * @param array $filters 筛选条件，支持：
     *   - organizations: 部门ID数组
     *   - qualifications: 资质ID数组
     *   - groups: 班组ID数组
     *   - availableSeats: 席位ID数组
     *   - temporaryRoles: 临时角色筛选（支持临时资质、组织、席位，格式见说明）
     * @param bool $isDetail 是否返回完整信息（包含关联数据）
     * @return array 筛选后的员工列表
     * @throws Exception 当查询失败时抛出异常
     */
    public function searchStaff(array $filters, bool $isDetail = false): array {
        try {
            // 基础SQL：查询员工主表，使用DISTINCT避免多关联导致的重复记录
            $baseSql = "SELECT DISTINCT s.guid, s.id, s.name 
                    FROM {$this->tables['staff']} s";
            $conditions = []; // 存储WHERE条件
            $params = []; // 存储SQL参数
            $joinTables = []; // 记录已关联的表，避免重复JOIN
            
            // 1. 处理部门筛选（关联组织表）
            if (!empty($filters['organizations'])) {
                $joinTables[] = "{$this->tables['organizations']} o";
                $baseSql .= " JOIN {$this->tables['organizations']} o ON s.guid = o.person_guid";
                $orgPlaceholders = [];
                foreach ($filters['organizations'] as $i => $orgId) {
                    $key = ":org_{$i}";
                    $orgPlaceholders[] = $key;
                    $params[$key] = $orgId;
                }
                $conditions[] = "o.organization_id IN (" . implode(',', $orgPlaceholders) . ")";
            }
            
            // 2. 处理资质筛选（关联资质表）
            if (!empty($filters['qualifications'])) {
                $joinTables[] = "{$this->tables['qualifications']} q";
                $baseSql .= " JOIN {$this->tables['qualifications']} q ON s.guid = q.person_guid";
                $qualPlaceholders = [];
                foreach ($filters['qualifications'] as $i => $qualId) {
                    $key = ":qual_{$i}";
                    $qualPlaceholders[] = $key;
                    $params[$key] = $qualId;
                }
                $conditions[] = "q.qualification_id IN (" . implode(',', $qualPlaceholders) . ")";
            }
            
            // 3. 处理班组筛选（关联群组表）
            if (!empty($filters['groups'])) {
                $joinTables[] = "{$this->tables['groups']} g";
                $baseSql .= " JOIN {$this->tables['groups']} g ON s.guid = g.person_guid";
                $groupPlaceholders = [];
                foreach ($filters['groups'] as $i => $groupId) {
                    $key = ":group_{$i}";
                    $groupPlaceholders[] = $key;
                    $params[$key] = $groupId;
                }
                $conditions[] = "g.group_id IN (" . implode(',', $groupPlaceholders) . ")";
            }
            
            // 4. 处理席位筛选（关联可用席位表）
            if (!empty($filters['availableSeats'])) {
                $joinTables[] = "{$this->tables['available_seats']} a";
                $baseSql .= " JOIN {$this->tables['available_seats']} a ON s.guid = a.person_guid";
                $seatPlaceholders = [];
                foreach ($filters['availableSeats'] as $i => $seatId) {
                    $key = ":seat_{$i}";
                    $seatPlaceholders[] = $key;
                    $params[$key] = $seatId;
                }
                $conditions[] = "a.available_seat_id IN (" . implode(',', $seatPlaceholders) . ")";
            }
            
            // 5. 处理临时角色筛选（关联临时角色表）
            // $filters['temporaryRoles'] 格式示例：
            // [
            //   'qualifications' => ['临时资质1', '临时资质2'], // 临时资质筛选
            //   'organizations' => ['临时组织1'], // 临时组织筛选
            //   'availableSeats' => ['临时席位1'] // 临时席位筛选
            // ]
            if (!empty($filters['temporaryRoles']) && is_array($filters['temporaryRoles'])) {
                $tempRoleTable = $this->tables['temporary_roles']; // 假设临时角色表名为temporary_roles
                // 关联临时角色表（只关联一次）
                if (!in_array($tempRoleTable . ' tr', $joinTables)) {
                    $baseSql .= " JOIN {$tempRoleTable} tr ON s.guid = tr.person_guid";
                    $joinTables[] = "{$tempRoleTable} tr";
                }
                
                // 5.1 临时资质筛选
                if (!empty($filters['temporaryRoles']['qualifications'])) {
                    $tempQualPlaceholders = [];
                    foreach ($filters['temporaryRoles']['qualifications'] as $i => $qual) {
                        $key = ":temp_qual_{$i}";
                        $tempQualPlaceholders[] = "tr.temp_qualification_id = {$key}";
                        $params[$key] = $qual;
                    }
                    $conditions[] = "(" . implode(' OR ', $tempQualPlaceholders) . ")";
                }
                
                // 5.2 临时组织筛选
                if (!empty($filters['temporaryRoles']['organizations'])) {
                    $tempOrgPlaceholders = [];
                    foreach ($filters['temporaryRoles']['organizations'] as $i => $org) {
                        $key = ":temp_org_{$i}";
                        $tempOrgPlaceholders[] = "tr.temp_organization_id = {$key}";
                        $params[$key] = $org;
                    }
                    $conditions[] = "(" . implode(' OR ', $tempOrgPlaceholders) . ")";
                }
                
                // 5.3 临时席位筛选
                if (!empty($filters['temporaryRoles']['availableSeats'])) {
                    $tempSeatPlaceholders = [];
                    foreach ($filters['temporaryRoles']['availableSeats'] as $i => $seat) {
                        $key = ":temp_seat_{$i}";
                        $tempSeatPlaceholders[] = "tr.temp_available_seat_id = {$key}";
                        $params[$key] = $seat;
                    }
                    $conditions[] = "(" . implode(' OR ', $tempSeatPlaceholders) . ")";
                }
            }
            
            // 拼接WHERE条件
            if (!empty($conditions)) {
                $baseSql .= " WHERE " . implode(' AND ', $conditions);
            }
            
            // 排序
            $baseSql .= " ORDER BY s.name ASC";
            
            // 执行查询获取符合条件的员工GUID和基本信息
            $stmt = $this->pdo->prepare($baseSql);
            $stmt->execute($params);
            $matchingStaff = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            if (empty($matchingStaff)) {
                return []; // 无匹配结果
            }
            
            // 如果需要完整信息，补充关联数据
            if ($isDetail) {
                $detailStaff = [];
                foreach ($matchingStaff as $staff) {
                    $detail = $this->getStaffByGuid($staff['guid']);
                    if ($detail) {
                        $detailStaff[] = $detail;
                    }
                }
                return $detailStaff;
            }
            
            // 否则返回基本列表（guid、id、name）
            return $matchingStaff;
            
        } catch (Exception $e) {
            throw new Exception("筛选员工失败: " . $e->getMessage());
        }
    }
    
    /**
     * 插入所有关联表数据
     */
    private function insertRelations($guid, $data) {
        // 处理资质
        if (!empty($data['qualifications'])) {
            $this->insertQualifications($guid, $data); //['qualifications']
        }
        
        // 处理组织
        if (!empty($data['organizations'])) {
            $this->insertOrganizations($guid, $data); //['organizations']
        }
        
        // 处理群组
        if (!empty($data['groups'])) {
            $this->insertGroups($guid, $data); //['groups']
        }
        
        // 处理可用席位
        if (!empty($data['availableSeats'])) {
            $this->insertAvailableSeats($guid, $data); //['availableSeats']
        }
        
        // 处理标记
        if (!empty($data['marks'])) {
            $this->insertMarks($guid, $data);  //['marks']
        }
        
        // 处理临时角色
        if (!empty($data['temporaryRoles'])) {
            $this->insertTemporaryRoles($guid, $data['temporaryRoles']);
        }
    }
    
    /**
     * 删除所有关联表数据
     */
    private function deleteRelations($guid) {
        $tables = [
            $this->tables['qualifications'],
            $this->tables['organizations'],
            $this->tables['groups'],
            $this->tables['available_seats'],
            $this->tables['marks'],
            $this->tables['temporary_roles']
        ];
        
        foreach ($tables as $table) {
            $stmt = $this->pdo->prepare("DELETE FROM $table WHERE person_guid = :guid");
            $stmt->execute([':guid' => $guid]);
        }
    }
    
    /**
     * 处理资质相关操作
     */
    private function insertQualifications($guid, $data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->tables['qualifications']} 
            (person_guid, qualification, qualification_id)
            VALUES (:guid, :qualification, :qualification_id)
        ");
        // echo '<br/>insertQualifications<br/>';
        // echo json_encode($data,256);
        foreach ($data['qualifications'] as $index => $name) {
            $stmt->execute([
                ':guid' => $guid,
                ':qualification' => $name,
                ':qualification_id' => $data['qualificationIds'][$index]
            ]);
        }
    }
    
    private function getQualifications($guid) {
        $stmt = $this->pdo->prepare("
            SELECT qualification, qualification_id 
            FROM {$this->tables['qualifications']}
            WHERE person_guid = :guid
        ");
        $stmt->execute([':guid' => $guid]);
        $result = $stmt->fetchAll();
        
        $qualifications = ['qualifications' => [], 'qualificationIds' => []];
        foreach ($result as $item) {
            $qualifications['qualifications'][] = $item['qualification'];
            $qualifications['qualificationIds'][] = $item['qualification_id'];
        }
        
        return $qualifications;
    }
    
    /**
     * 处理组织相关操作
     */
    private function insertOrganizations($guid, $data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->tables['organizations']} 
            (person_guid, organization, organization_id)
            VALUES (:guid, :organization, :organization_id)
        ");
        
        foreach ($data['organizations'] as $index => $name) {
            $stmt->execute([
                ':guid' => $guid,
                ':organization' => $name,
                ':organization_id' => $data['organizationsIds'][$index]
            ]);
        }
    }
    
    private function getOrganizations($guid) {
        $stmt = $this->pdo->prepare("
            SELECT organization, organization_id 
            FROM {$this->tables['organizations']}
            WHERE person_guid = :guid
        ");
        $stmt->execute([':guid' => $guid]);
        $result = $stmt->fetchAll();
        
        $organizations = ['organizations' => [], 'organizationsIds' => []];
        foreach ($result as $item) {
            $organizations['organizations'][] = $item['organization'];
            $organizations['organizationsIds'][] = $item['organization_id'];
        }
        
        return $organizations;
    }
    
    /**
     * 处理群组相关操作
     */
    private function insertGroups($guid, $data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->tables['groups']} 
            (person_guid, `group`, group_id)
            VALUES (:guid, :group, :group_id)
        ");
        
        foreach ($data['groups'] as $index => $name) {
            $stmt->execute([
                ':guid' => $guid,
                ':group' => $name,
                ':group_id' => $data['groupIds'][$index]
            ]);
        }
    }
    
    private function getGroups($guid) {
        $stmt = $this->pdo->prepare("
            SELECT `group`, group_id 
            FROM {$this->tables['groups']}
            WHERE person_guid = :guid
        ");
        $stmt->execute([':guid' => $guid]);
        $result = $stmt->fetchAll();
        
        $groups = ['groups' => [], 'groupIds' => []];
        foreach ($result as $item) {
            $groups['groups'][] = $item['group'];
            $groups['groupIds'][] = $item['group_id'];
        }
        
        return $groups;
    }
    
    /**
     * 处理可用席位相关操作
     */
    private function insertAvailableSeats($guid, $data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->tables['available_seats']} 
            (person_guid, available_seat, available_seat_id)
            VALUES (:guid, :available_seat, :available_seat_id)
        ");
        
        foreach ($data['availableSeats'] as $index => $name) {
            $stmt->execute([
                ':guid' => $guid,
                ':available_seat' => $name,
                ':available_seat_id' => $data['availableSeatIds'][$index]
            ]);
        }
    }
    
    private function getAvailableSeats($guid) {
        $stmt = $this->pdo->prepare("
            SELECT available_seat, available_seat_id 
            FROM {$this->tables['available_seats']}
            WHERE person_guid = :guid
        ");
        $stmt->execute([':guid' => $guid]);
        $result = $stmt->fetchAll();
        
        $seats = ['availableSeats' => [], 'availableSeatIds' => []];
        foreach ($result as $item) {
            $seats['availableSeats'][] = $item['available_seat'];
            $seats['availableSeatIds'][] = $item['available_seat_id'];
        }
        
        return $seats;
    }
    
    /**
     * 处理标记相关操作
     */
    private function insertMarks($guid, $data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->tables['marks']} 
            (person_guid, mark, mark_id)
            VALUES (:guid, :mark, :mark_id)
        ");
        
        foreach ($data['marks'] as $index => $name) {
            $stmt->execute([
                ':guid' => $guid,
                ':mark' => $name,
                ':mark_id' => $data['markIds'][$index]
            ]);
        }
    }
    
    private function getMarks($guid) {
        $stmt = $this->pdo->prepare("
            SELECT mark, mark_id 
            FROM {$this->tables['marks']}
            WHERE person_guid = :guid
        ");
        $stmt->execute([':guid' => $guid]);
        $result = $stmt->fetchAll();
        
        $marks = ['marks' => [], 'markIds' => []];
        foreach ($result as $item) {
            $marks['marks'][] = $item['mark'];
            $marks['markIds'][] = $item['mark_id'];
        }
        
        return $marks;
    }
    
    /**
     * 处理临时角色相关操作
     */
    private function insertTemporaryRoles($guid, $roles) {
        foreach ($roles as $role) {
            // 插入临时角色主表
            $stmt = $this->pdo->prepare("
                INSERT INTO {$this->tables['temporary_roles']} 
                (person_guid, from_time, to_time)
                VALUES (:guid, :from, :to)
            ");
            $stmt->execute([
                ':guid' => $guid,
                ':from' => $role['from'],
                ':to' => $role['to']
            ]);
            
            $roleId = $this->pdo->lastInsertId();
            
            // 插入临时角色资质
            if (!empty($role['qualifications'])) {
                $this->insertTemporaryRoleQualifications($roleId, $role);
            }
            
            // 插入临时角色组织
            if (!empty($role['organizations'])) {
                $this->insertTemporaryRoleOrganizations($roleId, $role);
            }
            
            // 插入临时角色可用席位
            if (!empty($role['availableSeats'])) {
                $this->insertTemporaryRoleSeats($roleId, $role);
            }
        }
    }
    
    // private function getTemporaryRoles($guid) {
    //     $stmt = $this->pdo->prepare("
    //         SELECT id, from_time, to_time 
    //         FROM {$this->tables['temporary_roles']}
    //         WHERE person_guid = :guid
    //     ");
    //     $stmt->execute([':guid' => $guid]);
    //     $roles = $stmt->fetchAll();
        
    //     $result = [];
    //     foreach ($roles as $role) {
    //         $tempRole = [
    //             'from' => $role['from_time'],
    //             'to' => $role['to_time'],
    //             'qualifications' => $this->getTemporaryRoleQualifications($role['id']),
    //             'organizations' => $this->getTemporaryRoleOrganizations($role['id']),
    //             'availableSeats' => $this->getTemporaryRoleSeats($role['id'])
    //         ];
    //         $result[] = $tempRole;
    //     }
        
    //     return $result;
    // }
    private function getTemporaryRoles($guid) {
        $stmt = $this->pdo->prepare("
            SELECT id, from_time, to_time 
            FROM {$this->tables['temporary_roles']}
            WHERE person_guid = :guid
        ");
        $stmt->execute([':guid' => $guid]);
        $roles = $stmt->fetchAll();
        
        $result = [];
        foreach ($roles as $role) {
            // 将时间格式从 "00:00:00" 改为 "00:00"
            $fromTime = substr($role['from_time'], 0, 5);
            $toTime = substr($role['to_time'], 0, 5);
            
            $tempRole = [
                'from' => $fromTime,
                'to' => $toTime,
                'qualifications' => $this->getTemporaryRoleQualifications($role['id']),
                'organizations' => $this->getTemporaryRoleOrganizations($role['id']),
                'availableSeats' => $this->getTemporaryRoleSeats($role['id'])
            ];
            $result[] = $tempRole;
        }
        
        return $result;
    }
    
    /**
     * 临时角色资质处理
     */
    private function insertTemporaryRoleQualifications($roleId, $data) {
        // echo '<br/>';
        // echo json_encode($data,256);
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->tables['temp_role_qualifications']} 
            (temporary_role_id, qualification, qualification_id)
            VALUES (:role_id, :qualification, :qualification_id)
        ");
        
        foreach ($data['qualifications'] as $index => $name) {
            $stmt->execute([
                ':role_id' => $roleId,
                ':qualification' => $name,
                ':qualification_id' => $data['qualificationIds'][$index]
            ]);
        }
    }
    
    private function getTemporaryRoleQualifications($roleId) {
        $stmt = $this->pdo->prepare("
            SELECT qualification, qualification_id 
            FROM {$this->tables['temp_role_qualifications']}
            WHERE temporary_role_id = :role_id
        ");
        $stmt->execute([':role_id' => $roleId]);
        $result = $stmt->fetchAll();
        
        $qualifications = ['qualifications' => [], 'qualificationIds' => []];
        foreach ($result as $item) {
            $qualifications['qualifications'][] = $item['qualification'];
            $qualifications['qualificationIds'][] = $item['qualification_id'];
        }
        
        return $qualifications;
    }
    
    /**
     * 临时角色组织处理
     */
    private function insertTemporaryRoleOrganizations($roleId, $data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->tables['temp_role_organizations']} 
            (temporary_role_id, organization, organization_id)
            VALUES (:role_id, :organization, :organization_id)
        ");
        
        foreach ($data['organizations'] as $index => $name) {
            $stmt->execute([
                ':role_id' => $roleId,
                ':organization' => $name,
                ':organization_id' => $data['organizationsIds'][$index]
            ]);
        }
    }
    
    private function getTemporaryRoleOrganizations($roleId) {
        $stmt = $this->pdo->prepare("
            SELECT organization, organization_id 
            FROM {$this->tables['temp_role_organizations']}
            WHERE temporary_role_id = :role_id
        ");
        $stmt->execute([':role_id' => $roleId]);
        $result = $stmt->fetchAll();
        
        $organizations = ['organizations' => [], 'organizationsIds' => []];
        foreach ($result as $item) {
            $organizations['organizations'][] = $item['organization'];
            $organizations['organizationsIds'][] = $item['organization_id'];
        }
        
        return $organizations;
    }
    
    /**
     * 临时角色可用席位处理
     */
    private function insertTemporaryRoleSeats($roleId, $data) {
        $stmt = $this->pdo->prepare("
            INSERT INTO {$this->tables['temp_role_seats']} 
            (temporary_role_id, available_seat, available_seat_id)
            VALUES (:role_id, :available_seat, :available_seat_id)
        ");
        
        foreach ($data['availableSeats'] as $index => $name) {
            $stmt->execute([
                ':role_id' => $roleId,
                ':available_seat' => $name,
                ':available_seat_id' => $data['availableSeatIds'][$index]
            ]);
        }
    }
    
    private function getTemporaryRoleSeats($roleId) {
        $stmt = $this->pdo->prepare("
            SELECT available_seat, available_seat_id 
            FROM {$this->tables['temp_role_seats']}
            WHERE temporary_role_id = :role_id
        ");
        $stmt->execute([':role_id' => $roleId]);
        $result = $stmt->fetchAll();
        
        $seats = ['availableSeats' => [], 'availableSeatIds' => []];
        foreach ($result as $item) {
            $seats['availableSeats'][] = $item['available_seat'];
            $seats['availableSeatIds'][] = $item['available_seat_id'];
        }
        
        return $seats;
    }
}
?>
    